#! /usr/bin/env python  
# -*- coding: utf-8 -*-  
# __author__ = "shuke"  
# Date: 2018/5/13  
import hashlib   
import pymysql
import oss2  
from dbutils.pooled_db import PooledDB
import sys,os
sys.path.append(os.getcwd())
from src.utils.tool import Tool


# 下载文件
# 创建数据库连接池
tempPool = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=6,  # 连接池允许的最大连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接
    maxcached=5,  # 链接池中最多闲置的链接
    maxshared=3,  # 链接池中最多共享的链接数量
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待
    host='192.168.10.191',
    port=3306,
    user='writer',
    password='zlc1002020',
    database='wisdom-exam-db',
    charset='utf8mb4'  # 设置编码
)

# 获取数据
def getScriptFiles():
    conn = tempPool.connection()
    cursor = conn.cursor()
    # 执行SQL语句
    sql = "SELECT id,path FROM script_files WHERE is_download = 1 AND download_status = 1 AND is_upload = 0 ORDER BY id ASC limit 100"
    cursor.execute(sql)
    # 获取查询结果
    result = cursor.fetchall()
    # 不关闭连接，而是将连接放回连接池
    cursor.close()
    conn.close()
    return result

# 更新上传状态   
def updateUpload(id,is_upload,oss_path):
    conn = tempPool.connection()
    cursor = conn.cursor()
    # 编写更新语句
    update_sql = "UPDATE script_files SET is_upload = %s,oss_path= %s WHERE id="+str(id)
    # 定义更新的值
    values = (is_upload,oss_path)
    # 执行更新语句
    cursor.execute(update_sql,values)
    # 提交事务
    conn.commit()
    # 关闭游标和数据库连接
    cursor.close()
    conn.close()

def runUpload():
    i = 1
    root_path = 'D:/wwwroot/Python/resources/topics/'
    # 填写你的Access Key ID和Access Key Secret
    auth = oss2.Auth('LTAI4GGWr4nfzQKFNb7i8Wge', '2UHWDB5LcsOGiy5SXt5GazUIkr4tah')
    # 填写Bucket信息
    bucket = oss2.Bucket(auth, 'oss-cn-shenzhen.aliyuncs.com', 'zlc100')
    while i <= 50000:
        # 获取试题
        lists = getScriptFiles()     
        # id,path
        for row in lists:
            
            # 异常捕捉
            try:
                # 要上传的文件名和文件路径
                file_url = Tool.str_replace(row[1],'http://file.eep.qctchina.top/','')
                file_url = Tool.str_replace(file_url,' ','')
                object_name = 'topics/'+file_url
                file_path = root_path+file_url
                result = bucket.put_object_from_file(object_name, file_path)
                updateUpload(row[0],'1',object_name)
                print(f"{object_name} 上传成功。")

            except OSError as e:
                updateUpload(row[0],'2','')
                print(f"图片上传阿里云OSS失败时出错: {e}") 

runUpload()                
    


 




    
    
    